unit main;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, sqlite3conn, sqldb, sqlite3ds, db, FileUtil, LResources,
  Forms, Controls, Graphics, Dialogs, ActnList, Menus, ComCtrls, ExtCtrls,
  StdCtrls, Buttons, ExtDlgs, DbCtrls, DataModule, frmyesno;

type

  { TFormMain }

  TFormMain = class(TForm)
    MemoMain: TMemo;
    Splitter1: TSplitter;
    TreeMain: TTreeView;
    procedure FormCreate(Sender: TObject);
    procedure TreeMainChange(Sender: TObject; Node: TTreeNode);
    procedure TreeMainChanging(Sender: TObject; Node: TTreeNode;
      var AllowChange: Boolean);
    procedure TreeMainExpanding(Sender: TObject; Node: TTreeNode;
      var AllowExpansion: Boolean);
    procedure TreeExpand( Node : TTreeNode);
  private
    { private declarations }
  public
    { public declarations }
  end;

var
  FormMain: TFormMain;

const
  DefaultBaseName='alex.db';

implementation

{ TFormMain }

procedure TFormMain.FormCreate(Sender: TObject);
var
   isFile: boolean;
begin
  isFile:=FileExists(DefaultBaseName);

  DM.SQL_C.DatabaseName:=DefaultBaseName;
  try
    DM.SQL_C.Connected:=TRUE;
  except
    if NOT isFile then begin
       ShowMessage('Не могу создать базу данных');
       halt;
    end else begin
       ShowMessage('Не могу подключиться к базе данных');
       halt;
    end;
  end;
  try
    DM.SQL_T.Active:=true;
  except
    ShowMessage(' Не могу создать транзакцию ');
    halt;
  end;
  if NOT isFile then begin
    try
      DM.SQL_Q.Close;
      DM.SQL_Q.SQL.Clear;
      DM.SQL_Q.SQL.Add('CREATE TABLE BTree (IDTree INTEGER PRIMARY KEY AUTOINCREMENT, Parent INT, CCount INT, Name TEXT);');
      DM.SQL_Q.ExecSQL;
      DM.SQL_Q.Close;
      DM.SQL_Q.SQL.Clear;
      DM.SQL_Q.SQL.Add('CREATE TABLE Notes (IDNotes INTEGER PRIMARY KEY AUTOINCREMENT, TreeID INTEGER, Note BLOB);');
      DM.SQL_Q.ExecSQL;
      DM.SQL_Q.Close;
      DM.SQL_Q.SQL.Clear;
      DM.SQL_Q.SQL.Add('INSERT INTO BTree VALUES (NULL, 0, 0, ''Новая запись'');');
      DM.SQL_Q.ExecSQL;
      DM.SQL_Q.Close;
      DM.SQL_Q.SQL.Clear;
      DM.SQL_Q.SQL.Add('CREATE TRIGGER insert_BTree INSERT ON BTree ');
      DM.SQL_Q.SQL.Add('BEGIN ');
      DM.SQL_Q.SQL.Add('UPDATE BTree SET CCount=CCount+1 WHERE IDTree=NEW.Parent; ');
      DM.SQL_Q.SQL.Add('END;');
      DM.SQL_Q.ExecSQL;
      DM.SQL_Q.Close;
      DM.SQL_Q.SQL.Clear;
      DM.SQL_Q.SQL.Add('CREATE TRIGGER delete_BTree DELETE ON BTree ');
      DM.SQL_Q.SQL.Add('BEGIN ');
      DM.SQL_Q.SQL.Add('UPDATE BTree SET CCount=CCount-1 WHERE IDTree=OLD.Parent; ');
      DM.SQL_Q.SQL.Add('DELETE FROM Notes WHERE Notes.TreeID = OLD.IDTree; ');
      DM.SQL_Q.SQL.Add('END;');
      DM.SQL_Q.ExecSQL;
      DM.SQL_Q.Close;
      DM.SQL_Q.SQL.Clear;
      DM.SQL_Q.SQL.Add('CREATE TRIGGER update_BTree UPDATE ON BTree ');
      DM.SQL_Q.SQL.Add('BEGIN ');
      DM.SQL_Q.SQL.Add('UPDATE BTree SET CCount=CCount-1 WHERE IDTree=OLD.Parent AND OLD.Parent <> NEW.Parent; ');
      DM.SQL_Q.SQL.Add('UPDATE BTree SET CCount=CCount+1 WHERE IDTree=NEW.Parent AND OLD.Parent <> NEW.Parent; ');
      DM.SQL_Q.SQL.Add('END;');
      DM.SQL_Q.ExecSQL;
      DM.SQL_T.Commit;
    except
      ShowMessage('Ошибка при создании таблиц БД.');
      DM.SQL_T.Rollback;
      DeleteFile(DefaultBaseName);
      halt;
    end;
  end;
  TreeExpand(nil);
  TreeMainChange(nil, nil);
end;

procedure TFormMain.TreeMainChange(Sender: TObject; Node: TTreeNode);
var
  Stream: TStream;
begin
  DM.SQL_Q.Close;
  DM.SQL_Q.SQL.Clear;
  DM.SQL_Q.SQL.Add ('SELECT * FROM Notes WHERE TreeID ='+IntToStr(Integer(FormMain.TreeMain.Selected.Data))+';');
  DM.SQL_Q.Open;
  if DM.SQL_Q.FieldByName('TreeID').AsInteger = Integer(FormMain.TreeMain.Selected.Data) then begin
    Stream:=DM.SQL_Q.CreateBlobStream(DM.SQL_Q.FieldByName('Note'), bmRead);
    try
      MemoMain.Lines.LoadFromStream(Stream);
    finally
      Stream.Free;
    end;
  end else begin
    MemoMain.Lines.Clear;
  end;
  MemoMain.Modified:=FALSE;
end;

procedure TFormMain.TreeMainChanging(Sender: TObject; Node: TTreeNode;var AllowChange: Boolean);
begin
  if MemoMain.Modified then begin
    Application.CreateForm(TFormYesNo, FormYesNo);
    FormYesNo.ShowModal;
    if FormYesNo.isYesNo then begin
      DM.SQL_Q.Close;
      DM.SQL_Q.SQL.Clear;
      DM.SQL_Q.SQL.Add('SELECT * FROM Notes WHERE TreeID= '+IntToStr(Integer(FormMain.TreeMain.Selected.Data))+';');
      DM.SQL_Q.Open;
      if DM.SQL_Q.RecordCount = 0 then begin
        DM.SQL_Q.Close;
        DM.SQL_Q.SQL.Clear;
        DM.SQL_Q.SQL.Add('INSERT INTO Notes VALUES (NULL,'+IntToStr(Integer(FormMain.TreeMain.Selected.Data))+', '''+FormMain.MemoMain.Lines.Text+''');');
        DM.SQL_Q.ExecSQL;
        DM.SQL_T.Commit;
      end else begin
        DM.SQL_Q.Close;
        DM.SQL_Q.SQL.Clear;
        DM.SQL_Q.SQL.Add('UPDATE Notes SET Note='''+FormMain.MemoMain.Lines.Text+''' WHERE TreeID='+IntToStr(Integer(FormMain.TreeMain.Selected.Data))+';');
        DM.SQL_Q.ExecSQL;
        DM.SQL_T.Commit;
      end;
    end;
    FormYesNo.Free;
    MemoMain.Modified:=FALSE;
  end;
end;

procedure TFormMain.TreeExpand( Node : TTreeNode);
var
   ID , i   : Integer;
   TreeNode : TTreeNode;
begin
    if Node = nil then ID:=0
    else ID:=Integer(Node.Data);

    DM.SQL_Q.Close;
    DM.SQL_Q.SQL.Clear;
    DM.SQL_Q.SQL.Add ('SELECT * FROM BTree WHERE Parent='+IntToStr(ID)+';');
    DM.SQL_Q.Open;

    TreeMain.Items.BeginUpdate;
    for i:=1 to DM.SQL_Q.RecordCount do
    begin
       TreeNode:=TreeMain.Items.AddChildObject(Node ,
                                  DM.SQL_Q.FieldByName('Name').asString ,
                                  Pointer(DM.SQL_Q.FieldByName('IDTree').asInteger));
         TreeNode.ImageIndex:=1;
         TreeNode.SelectedIndex:=2;
        if DM.SQL_Q.FieldByName('CCount').asInteger > 0 then begin
           TreeMain.Items.AddChildObject(TreeNode , '' , nil);
        end;
       DM.SQL_Q.Next;
    end;
    TreeMain.Items.EndUpdate;
    DM.StepParent(ID);
end;

procedure TFormMain.TreeMainExpanding(Sender: TObject; Node: TTreeNode;
  var AllowExpansion: Boolean);
begin
  if Node.getFirstChild.Data = nil then begin
          Node.DeleteChildren;
          TreeExpand(Node);
   end;
end;



initialization
  {$I main.lrs}

end.

